package com.ph.irp.base.measureunitgroup.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.measureunitgroup.dao.PBaseMeasureUnitGroupDAO;
import com.ph.irp.model.PBaseMeasuregroup;
import com.ph.irp.model.PBaseMeasureunit;
import com.ph.util.DBUtil;

/**
 * 计量单位组
 * 
 * @author weide
 * @version 1.0 <br>
 *          Copyright (C), 2009, SongYandong <br>
 *          This program is protected by copyright laws. <br>
 *          Program Name: PBaseMeasureUnitGroupDAOImpl.java<br>
 *          Date:2009-10-27
 */
public class PBaseMeasureUnitGroupDAOImpl implements PBaseMeasureUnitGroupDAO {

	/** The data source. */
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	/*
	 * 添加
	 * 
	 * 
	 */
	@Override
	public void addMeasureGroupUnit(PBaseMeasuregroup measureGroupModel) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			String sql = "insert into P_base_measureGroup (measureGroupCode,measureGroupName) values(?,?)";

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sql);
			pStmt.setString(1, measureGroupModel.getMeasureGroupCode().trim());
			pStmt.setString(2, measureGroupModel.getMeasureGroupName().trim());

			pStmt.executeUpdate();

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	/*
	 * 查询所有
	 * 
	 * @param measureGroupModel
	 * 
	 * @return List
	 */
	@Override
	public List<PBaseMeasuregroup> getMeasureGroupAll()throws Throwable{

		List<PBaseMeasuregroup> measureList = new ArrayList<PBaseMeasuregroup>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con.prepareStatement("select * from P_base_measureGroup");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

		return measureList;
	}
	/*
	 * 查询byName
	 * 
	 * 
	 */
	public List<PBaseMeasuregroup> getMeasureGroupByName(String name)throws Throwable{
		
		List<PBaseMeasuregroup> measureList = new ArrayList<PBaseMeasuregroup>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureGroup where measureGroupName like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%"+name+"%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
	}
		return measureList;
	}	
	/*
	 * 查询byCode
	 * 
	 * 
	 */
	public List<PBaseMeasuregroup> getMeasureGroupByCode(String code)throws Throwable{

		List<PBaseMeasuregroup> measureList = new ArrayList<PBaseMeasuregroup>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureGroup where measureGroupCode like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%"+code+"%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
	}
		return measureList;
	}
	/*
	 * 查询byNameAndCode
	 * 
	 * 
	 */
	public List<PBaseMeasuregroup> getMeasureGroupByNameAndCode(String name,String code)throws Throwable{
		
		List<PBaseMeasuregroup> measureList = new ArrayList<PBaseMeasuregroup>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from P_base_measureGroup where measureGroupName like ? and measureGroupCode like ?");
			// 给宿主变量绑定值
			statement.setString(1, "%"+name+"%");
			statement.setString(2, "%"+code+"%");
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(measureList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return measureList;
	}
	
	/*
	 * Update unit group
	 * 
	 * @param measureGroupModel No return value
	 */
	@Override
	public void updateMeasureGroupUnit(PBaseMeasuregroup measureGroupModel) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
//			StringBuilder sb = new StringBuilder();
//			sb.append("update P_base_measureGroup set measureGroupCode = ?,");
//			sb.append("measureGroupName = ?" + "where measureGroupId = ?");

			String sql = "update P_base_measureGroup set measureGroupCode = ?,measureGroupName = ? where measureGroupId = ?";
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setString(1, measureGroupModel.getMeasureGroupCode().trim());
			pStmt.setString(2, measureGroupModel.getMeasureGroupName().trim());
			pStmt.setInt(3, measureGroupModel.getMeasureGroupId());

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	/*
	 * Query a unit group
	 * 
	 * @param int
	 * 
	 * @return object
	 */
	@Override
	public PBaseMeasuregroup getMeasureGroupById(int measureGroupId) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseMeasuregroup mg = new PBaseMeasuregroup();

		try {
			String sql = "select * from P_base_measureGroup where measureGroupId=?";
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, measureGroupId);
			rs = pStmt.executeQuery();
			while (rs.next()) {
				mg.setMeasureGroupId(rs.getInt("measureGroupId"));
				mg.setMeasureGroupCode(rs.getString("measureGroupCode"));
				mg.setMeasureGroupName(rs.getString("measureGroupName"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return mg;
	}

	/**
	 * Delete Unit group
	 * @throws SQLException 
	 * 
	 * 
	 */
	@Override
	public void deleteMeasureGroup(int measureGroupId) throws Throwable{

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			String sql = "delete from P_base_measureGroup where measureGroupId=?";
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, measureGroupId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
	}


	/**
	 * 迭代结果集，将数据封装到measureList中
	 * 
	 * @param settlementList
	 * @param resultSet
	 * @return
	 * @throws Throwable
	 */
	public List<PBaseMeasuregroup> turnResultSetToList(
			List<PBaseMeasuregroup> measureList, ResultSet resultSet)
			throws Throwable {

		// 迭代结果集
		while (resultSet.next()) {

			PBaseMeasuregroup pbaseMeasuregroup = new PBaseMeasuregroup();

			pbaseMeasuregroup.setMeasureGroupId(resultSet.getInt("measureGroupId"));
			pbaseMeasuregroup.setMeasureGroupCode(resultSet.getString("measureGroupCode"));
			pbaseMeasuregroup.setMeasureGroupName(resultSet.getString("measureGroupName"));
			
			measureList.add(pbaseMeasuregroup);
		}

		return measureList;
	}
}
